package models

import (
	"encoding/json"
	"errors"
	"fmt"
	"strconv"
	"strings"
	"time"

	"k8s.io/apimachinery/pkg/util/sets"

	"cvevulner/common"

	"github.com/astaxie/beego/logs"
	"github.com/astaxie/beego/orm"
)

const (
	// StatusOpen indicates that the issue is currently open and being worked on.
	StatusOpen = 1

	// StatusProgress indicates that the issue is in progress.
	StatusProgress = 2

	// StatusSuspend indicates that the issue has been suspended.
	StatusSuspend = 5
)

type SaNuberCount struct {
	Count int64
}

func QueryOriginCveExcel(days string, prcnum int) ([]OriginExcel, int64, error) {
	o := orm.NewOrm()
	var coe []OriginExcel
	num, err := o.Raw("select cve_id,cve_num,cve_url, cve_version, pack_name,"+
		"score_type,nvd_score,cve_level,cve_desc,repair_time,vector_value,attack_vector,access_vector,attack_complexity,"+
		"access_complexity,privilege_required,user_interaction,scope,confidentiality,integrity,availability,authentication,"+
		"cve_status,create_time from cve_origin_excel where update_time >= ? and cve_status in (?, ?)"+
		"order by cve_id asc limit ?", days, 0, 1, prcnum).QueryRows(&coe)
	if err == nil && num > 0 {
		logs.Info("QueryOriginCveExcel, cve_origin_excel, search result: ", num)
	} else {
		logs.Info("QueryOriginCveExcel, There is currently no manually added or updated cve, cur_time:",
			common.GetCurTime(), ",err: ", err)
	}
	return coe, num, err
}

func UpdateCveIssueStatusById(status int, issueId int64) (id int64) {
	o := orm.NewOrm()
	_ = o.Raw("UPDATE cve_gite_origin_issue SET proc_status = ?, issue_update_at = ?"+
		" WHERE id = ?", status, common.GetCurTime(), issueId).QueryRow()
	return
}

func LockUpdateCveIssueStatus(status int, issueId int64) bool {
	o := orm.NewOrm()
	res, err := o.Raw("UPDATE cve_gite_origin_issue SET proc_status = ?, "+
		"issue_update_at = ? where id = ? and proc_status = 0",
		status, common.GetCurTime(), issueId).Exec()
	if err == nil {
		num, _ := res.RowsAffected()
		if num > 0 {
			logs.Info("LockUpdateCveIssueStatus, update cve_gite_origin_issue row affected nums: ", num,
				",issueId: ", issueId)
			return true
		}
		return false
	} else {
		logs.Error("LockUpdateCveIssueStatus, update failed, cve_gite_origin_issue, issueId: ",
			issueId, ", err: ", err)
		return false
	}
}

func UnLockUpdateCveIssueStatus(beforeStatus, afterStatus int8, updateTime, beforeTime string) bool {
	o := orm.NewOrm()
	res, err := o.Raw("UPDATE cve_gite_origin_issue SET proc_status = ?, "+
		"issue_update_at = ? where proc_status = ? and issue_update_at < ?",
		afterStatus, updateTime, beforeStatus, beforeTime).Exec()
	if err == nil {
		num, _ := res.RowsAffected()
		if num > 0 {
			logs.Info("UnLockUpdateCveIssueStatus, update cve_gite_origin_issue row affected nums: ", num)
			return true
		}
		return false
	} else {
		logs.Error("UnLockUpdateCveIssueStatus, update failed, cve_gite_origin_issue, err: ", err)
		return false
	}
}

func QueryIssueTemplateAll(prcnum int, tmplateId int64, beforeTime string) (ite []IssueTemplate, num int64, err error) {
	o := orm.NewOrm()
	num, err = o.Raw("select *"+
		" from cve_issue_template where template_id > ? and create_time > ? "+
		"order by template_id asc limit ?", tmplateId, beforeTime, prcnum).QueryRows(&ite)
	if err == nil && num > 0 {
		logs.Info("QueryIssueTemplateAll, cve_issue_template, search result: ", num)
	} else {
		logs.Error("QueryIssueTemplateAll, There are currently no new or updated issues, cve_issue_template, cur_time:",
			common.GetCurTime(), ",err: ", err)
	}
	return
}

func QueryIssueTemplateByNum(cveNum string) (ite []IssueTemplate, num int64, err error) {
	o := orm.NewOrm()
	num, err = o.Raw("select *"+
		" from cve_issue_template where cve_num = ? and status < ? ", cveNum, 4).QueryRows(&ite)
	if err == nil && num > 0 {
		logs.Info("QueryIssueTemplateByNum, cve_issue_template, search result: ", num)
	} else {
		logs.Error("QueryIssueTemplateByNum, There are currently no new or updated issues, cve_issue_template, cur_time:",
			common.GetCurTime(), ",err: ", err)
	}
	return
}

func QueryOriginCveIssue(prcnum int) ([]GiteOriginIssue, int64, error) {
	o := orm.NewOrm()
	var gs []GiteOriginIssue
	num, err := o.Raw("select *"+
		" from cve_gite_origin_issue where proc_status = ? "+
		"order by id asc limit ?", 0, prcnum).QueryRows(&gs)
	if err == nil && num > 0 {
		logs.Info("QueryOriginCveIssue, cve_gite_origin_issue, search result: ", num)
	} else {
		logs.Error("QueryOriginCveIssue, There are currently no new or updated issues, cve_gite_origin_issue, cur_time:",
			common.GetCurTime(), ",err: ", err, gs)
	}
	return gs, num, err
}

func QueryOriginCve(days string, prcnum, credibilityLevel int, list []string) ([]OriginUpstream, int64, error) {
	o := orm.NewOrm()
	var gs []OriginUpstream
	var num int64
	var err error
	if list != nil && len(list) > 0 {
		var s string
		for _, v := range list {
			s += "'" + v + "',"
		}
		if len(s) > 1 {
			s = s[:len(s)-1]
		}
		num, err = o.Raw(fmt.Sprintf("select * from cve_origin_upstream where cve_num in (%s) "+
			"and credibility_level <= %v and cve_status in (0, 1) "+
			"order by cve_id asc limit %v", s, credibilityLevel, prcnum)).QueryRows(&gs)
	} else {
		num, err = o.Raw("select * from cve_origin_upstream where cve_status in (?, ?) "+
			"and credibility_level <= ? "+
			"order by cve_id asc limit ?", 0, 1, credibilityLevel, prcnum).QueryRows(&gs)
	}
	if err == nil && num > 0 {
		//logs.Info("QueryOriginCveIssue, cve_origin_upstream, search result: ", num)
	} else {
		logs.Info("QueryOriginCveIssue, There are currently no new or updated automated submission cve, cur_time:",
			common.GetCurTime(), ", err: ", err)
	}
	return gs, num, err
}

func QueryCveDesc(cveId int64) (OriginUpstreamDesc, bool) {
	o := orm.NewOrm()
	var cveDesc OriginUpstreamDesc
	err := o.Raw("select * from cve_origin_upstream_desc where cve_id = ?", cveId).QueryRow(&cveDesc)
	if err != nil {
		logs.Error(cveId, "QueryCveDesc, "+
			"The original cve description cannot be queried, cveId: ", cveId, ", err: ", err)
		return cveDesc, false
	} else {
		return cveDesc, true
	}
}

// QueryCveImpact get impact by cveid and source
func QueryCveImpact(cveId int64, source string) (OriginUpstreamImpact, bool) {
	o := orm.NewOrm()
	var cveImpact OriginUpstreamImpact
	err := o.Raw("select * from cve_origin_upstream_impact where cve_id = ? and source = ?", cveId, source).
		QueryRow(&cveImpact)
	if err != nil {
		logs.Error(cveId, "QueryCveImpact, cve_origin_upstream_impact, cveId: ", cveId, ",err: ", err)
		return cveImpact, false
	} else {
		return cveImpact, true
	}
}

func QueryCveScore(impactId int64, typex string) (OriginUpstreamImpactScore, bool) {
	o := orm.NewOrm()
	var cveScore OriginUpstreamImpactScore
	var err error
	if typex == "v3" {
		err = o.Raw(
			"select * from cve_origin_upstream_impact_score where impact_id = ? and base_met_v3=? and cvss_v3=?",
			impactId, 1, 1,
		).QueryRow(&cveScore)
	} else {
		err = o.Raw(
			"select * from cve_origin_upstream_impact_score where impact_id = ?  and cvss_v4=?",
			impactId, 1,
		).QueryRow(&cveScore)
	}

	if err != nil {
		logs.Error(impactId, "QueryCveScore, cve_origin_upstream_impact_score, impactId: ", impactId, ", err: ", err)
	}

	return cveScore, err == nil

}

func QueryCveCvssV3(scoreId int64) (OriginUpstreamImpactScoreV3, bool) {
	o := orm.NewOrm()
	var cveScoreV3 OriginUpstreamImpactScoreV3
	err := o.Raw(
		"select * from cve_origin_upstream_impact_score_v3 where score_id = ?", scoreId,
	).QueryRow(&cveScoreV3)
	if err != nil {
		logs.Error("QueryCveCvssV3, cve_origin_upstream_impact_score_v3, scoreId: ", scoreId, ", err: ", err)
	}

	return cveScoreV3, err == nil
}

func QueryCveCvssV2(scoreId int64) (OriginUpstreamImpactScoreV2, bool) {
	o := orm.NewOrm()
	var cveScoreV2 OriginUpstreamImpactScoreV2
	err := o.Raw(
		"select * from cve_origin_upstream_impact_score_v2 where score_id = ?", scoreId,
	).QueryRow(&cveScoreV2)
	if err != nil {
		logs.Error("QueryCveCvssV2, cve_origin_upstream_impact_score_v2, scoreId: ", scoreId, ", err: ", err)
	}

	return cveScoreV2, err == nil
}

// QueryCveCvssV4 find cvss4 data
func QueryCveCvssV4(scoreId int64) (OriginUpstreamImpactScoreV4, bool) {
	o := orm.NewOrm()
	var cveScoreV4 OriginUpstreamImpactScoreV4
	err := o.Raw(
		"select * from cve_origin_upstream_impact_score_v4 where score_id = ?", scoreId,
	).QueryRow(&cveScoreV4)
	if err != nil {
		logs.Error("QueryCveCvssV4, cve_origin_upstream_impact_score_v4, scoreId: ", scoreId, ", err: ", err)
	}

	return cveScoreV4, err == nil
}

func QueryCveByPackName(cveNum, packName string, organizateId int8) (vc []VulnCenter) {
	o := orm.NewOrm()
	num, err := o.Raw("select * from cve_vuln_center where cve_num = ? and "+
		"pack_name = ? and organizate_id = ? order by cve_id desc",
		cveNum, packName, organizateId).QueryRows(&vc)
	if err != nil {
		logs.Error("QueryCveByPackName, cve_vuln_center, num: ", num, ", err: ", err)
	}
	return
}

func QueryCveByNum(cveNum, packName, pkgname string, organizateId int8) (VulnCenter, bool) {
	o := orm.NewOrm()
	var cve VulnCenter
	// binary : strictly case sensitive SQL search
	err := o.Raw("select * from cve_vuln_center where cve_num = ? and "+
		"binary pack_name = ? and organizate_id = ? and repo_name = ? order by cve_id desc limit 1",
		cveNum, packName, organizateId, pkgname).QueryRow(&cve)
	if err != nil || cve.CveId == 0 {
		logs.Error("QueryCveByNum, cve_vuln_center, cveNum: ",
			cveNum, ",packName: ", packName, ", err: ", err)
		return cve, false
	} else {
		return cve, true
	}
}

func UpdateCveStatusExportByNum(updatetime, cveNum string, isExport int, repoPath, version string) bool {
	o := orm.NewOrm()
	res, err := o.Raw("UPDATE cve_vuln_center SET "+
		"is_export = ?, update_time = ? where cve_num = ? and pack_name = ? and cve_version = ?",
		isExport, updatetime, cveNum, repoPath, version).Exec()
	if err == nil {
		num, _ := res.RowsAffected()
		if num > 0 {
			logs.Info("cve_vuln_center row affected nums: ", num,
				",cveNum: ", cveNum, ",updatetime: ", updatetime)
			return true
		}
		return false
	} else {
		logs.Error("UpdateCveStatusExportByNum, cve_vuln_center, Update failed, cveNum: ", cveNum,
			", updatetime: ", updatetime, ", err: ", err)
		return false
	}
}

func QueryScoreByCveId(CveId int64) (Score, bool) {
	o := orm.NewOrm()
	var score Score
	err := o.Raw("select * from cve_score where cve_id = ?", CveId).QueryRow(&score)
	if err != nil {
		logs.Error(CveId, "QueryScoreByCveId, cve_id: ", CveId, ",err: ", err)
		return score, false
	} else {
		return score, true
	}
}

func QueryOpenEulerSAByCveId(CveId int64) (OpenEulerSA, bool) {
	o := orm.NewOrm()
	op := OpenEulerSA{CveId: CveId}
	err := o.Read(&op, "CveId")
	if err == orm.ErrNoRows {
		logs.Error("QueryOpenEulerSAByCveId, cveId: ", CveId, ", err: ", err)
		return op, false
	} else if err == orm.ErrMissPK {
		logs.Error("QueryOpenEulerSAByCveId, cveId: ", CveId, ", err: ", err)
		return op, false
	} else {
		return op, true
	}
}

func QuerySecNoticeByCveId(CveId int64) (SecurityNotice, bool) {
	o := orm.NewOrm()
	var secNotice SecurityNotice
	err := o.Raw("select * from cve_security_notice where cve_id = ?", CveId).QueryRow(&secNotice)
	if err != nil {
		logs.Error("QuerySecNoticeByCveId, cveId: ", CveId, ", err: ", err)
		return secNotice, false
	} else {
		return secNotice, true
	}
}

func CreateSecurityNotice(sec *SecurityNotice) (SecId int64, err error) {
	o := orm.NewOrm()
	errs := o.Begin()
	if errs == nil {
		var num int64
		if num, err = o.Insert(sec); err == nil {
			logs.Info("CreateSecurityNotice, insert cve_security_notice success, num: ", num, ",cveNum: ", sec.CveNum)
		} else {
			logs.Error("CreateSecurityNotice, insert cve_security_notice failed, cveNum:", sec.CveNum, ", err: ", err)
			err = o.Rollback()
			return 0, err
		}
		SecId = sec.SecId
		_ = o.Commit()
	} else {
		logs.Error("CreateSecurityNotice, Transaction creation failed, cveNum: ", sec.CveNum, ", errs: ", errs)
		return 0, errs
	}
	return SecId, nil
}

func CreateScore(score *Score) (Id int64, err error) {
	o := orm.NewOrm()
	errs := o.Begin()
	if errs == nil {
		var num int64
		if num, err = o.Insert(score); err == nil {
			logs.Info("CreateScore, insert cve_score success, num: ", num, ",cveNum: ", score.CveNum)
		} else {
			logs.Error("CreateScore, insert cve_score failed, score: ", score, ", err: ", err)
			o.Rollback()
			return 0, err
		}
		Id = score.Id
		o.Commit()
	} else {
		logs.Error("CreateScore, Transaction creation failed, cveNum:", score.CveNum, ",errs: ", errs)
		return 0, errs
	}
	return Id, nil
}

func CreateOpenEulerSA(op *OpenEulerSA) (OpenId int64, err error) {
	o := orm.NewOrm()
	errs := o.Begin()
	if errs == nil {
		var num int64
		if num, err = o.Insert(op); err == nil {
			logs.Info("CreateOpenEulerSA, insert cve_open_euler_s_a success, num: ", num, "CveId:", op.CveId)
		} else {
			logs.Error("CreateOpenEulerSA, insert cve_open_euler_s_a failed, CveId: ", op.CveId, ", err:", err)
			o.Rollback()
			return 0, err
		}
		OpenId = op.OpenId
		o.Commit()
	} else {
		logs.Error("CreateOpenEulerSA, Transaction creation failed,cveId:", op.CveId, ", errs: ", errs)
		return 0, errs
	}
	return OpenId, nil
}

func QueryIssueScoreRecord(cveId int64, status int8) (ScoreRecord, error) {
	o := orm.NewOrm()
	var sr ScoreRecord
	err := o.Raw("select id, cve_id, nvd_score, n_vector_value"+
		" from cve_score_record where cve_id = ? and status = ? order by id desc limit 1",
		cveId, status).QueryRow(&sr)
	if err == nil {
		logs.Info("QueryIssueScoreRecord, cve_score_record, search result: ", sr)
	} else {
		logs.Info("QueryIssueScoreRecord, cve_score_record, cveId: ", cveId, ", err: ", err)
	}
	return sr, err
}

func InsertScoreRecord(sc *ScoreRecord) (scoreId int64, err error) {
	o := orm.NewOrm()
	errs := o.Begin()
	scoreId = 0
	if errs == nil {
		if num, err := o.Insert(sc); err == nil {
			logs.Info("InsertScoreRecord, insert cve_score_record, num:", num, ", CveId:", sc.CveId)
			scoreId = num
		} else {
			logs.Error("InsertScoreRecord, insert cve_score_record failed, CveId:", sc.CveId, ",err: ", err)
			o.Rollback()
			return scoreId, err
		}
		o.Commit()
	} else {
		logs.Error("InsertScoreRecord, Transaction creation failed, cveId:", sc.CveId, ",errs: ", errs)
		return scoreId, errs
	}
	return scoreId, nil
}

func UpdateScoreRecord(sc *ScoreRecord, cols ...string) (resultNum int64, err error) {
	o := orm.NewOrm()
	errs := o.Begin()
	resultNum = 0
	if errs == nil {
		if num, err := o.Update(sc, cols...); err == nil {
			logs.Info("UpdateScoreRecord, update cve_score_record, num:", num, ", CveId:", sc.CveId)
			resultNum = num
		} else {
			logs.Error("UpdateScoreRecord, update cve_score_record failed, CveId:", sc.CveId, ",err: ", err)
			o.Rollback()
			return resultNum, err
		}
		o.Commit()
	} else {
		logs.Error("InsertScoreRecord, Transaction creation failed, cveId:", sc.CveId, ",errs: ", errs)
		return resultNum, errs
	}
	return resultNum, nil
}

func InsertScore(score *Score) error {
	o := orm.NewOrm()
	err := o.Begin()
	if err == nil {
		var num int64
		if num, err = o.Insert(score); err == nil {
			logs.Info("InsertScore, insert cve_score success, num: ", num, ", cveNum: ", score.CveNum)
		} else {
			logs.Error("InsertScore, insert cve_score failed, CveNum:", score.CveNum, ",err: ", err)
			o.Rollback()
			return err
		}
		score.Id = num
		o.Commit()
	}
	return err
}

func CreateCveRelat(cve *VulnCenter, sec *SecurityNotice, score *Score,
	op *OpenEulerSA, sc *ScoreRecord) (cveid int64, err error) {
	o := orm.NewOrm()
	errs := o.Begin()
	if errs == nil {
		var num int64
		var err error
		if num, err = o.Insert(cve); err == nil {
			logs.Info("CreateCveRelat, insert cve_vuln_center success, num: ", num, ", cveNum: ", cve.CveNum)
		} else {
			logs.Error("CreateCveRelat, insert cve_vuln_center failed, CveNum: ", cve.CveNum, ",err: ", err)
			o.Rollback()
			return 0, err
		}
		sec.CveId = num
		cveid = num
		op.CveId = cveid
		cve.CveId = num
		if num, err = o.Insert(op); err == nil {
			logs.Info("CreateCveRelat, insert cve_open_euler_s_a success, num: ", num, ",cveNum: ", cve.CveNum)
		} else {
			logs.Error("CreateCveRelat, insert cve_open_euler_s_a failed, CveNum:", cve.CveNum, ",err: ", err)
			o.Rollback()
			return 0, err
		}
		sec.OpenId = num
		score.OpenId = num
		if num, err = o.Insert(sec); err == nil {
			logs.Info("CreateCveRelat, insert cve_security_notice success, num: ", num, ", cveNum: ", cve.CveNum)
		} else {
			logs.Error("CreateCveRelat, insert cve_security_notice failed, cveNum:", cve.CveNum, ",err: ", err)
			o.Rollback()
			return 0, err
		}
		score.CveId = cveid
		if num, err = o.Insert(score); err == nil {
			logs.Info("CreateCveRelat, insert cve_score success, num: ", num, ", cveNum: ", cve.CveNum)
		} else {
			logs.Error("CreateCveRelat, insert cve_score failed, CveNum:", cve.CveNum, ",err: ", err)
			o.Rollback()
			return 0, err
		}
		sc.CveId = cveid
		if num, err = o.Insert(sc); err == nil {
			logs.Info("CreateCveRelat, insert cve_score_record, num: ", num, ", CveId:", cve.CveId)
		} else {
			logs.Error("CreateCveRelat, insert cve_score_record failed, CveId:", cve.CveId, ",err: ", err)
			o.Rollback()
			return 0, err
		}
		o.Commit()
	} else {
		logs.Error("CreateCveRelat, Transaction creation failed, CveId:", cve.CveId, ",err: ", errs)
		return 0, errs
	}
	return cveid, nil
}

func UpdateCveRelat(cve *VulnCenter, sec *SecurityNotice, score *Score) error {
	o := orm.NewOrm()
	errs := o.Begin()
	if errs == nil {
		var cv VulnCenter
		cvError := o.Raw("select cve_id from cve_vuln_center where cve_id = ?", cve.CveId).QueryRow(&cv)
		if cvError == nil {
			var num int64
			var err error
			cve.CveId = cv.CveId
			if num, err = o.Update(cve); err == nil {
				logs.Info("UpdateCveRelat, update cve_vuln_center success, num: ", num, ", cveNum", cve.CveNum)
			} else {
				logs.Error("UpdateCveRelat, update cve_vuln_center failed, CveId:", cve.CveId, ",err: ", err)
				o.Rollback()
				return err
			}
		} else {
			var num int64
			var err error
			if num, err = o.Insert(cve); err == nil {
				logs.Info("UpdateCveRelat, insert cve_vuln_center success, num: ", num, ", cveNum:", cve.CveNum)
			} else {
				logs.Error("UpdateCveRelat, insert cve_vuln_center failed, CveNum:", cve.CveNum, ",err:", err)
				o.Rollback()
				return err
			}

		}
		var se SecurityNotice
		seError := o.Raw("select sec_id from cve_security_notice where cve_id = ?", cve.CveId).QueryRow(&se)
		if seError == nil {
			var num int64
			sec.SecId = se.SecId
			var err error
			if num, err = o.Update(sec); err == nil {
				logs.Info("UpdateCveRelat, update cve_security_notice success, num: ", num, ", cve.CveId", cve.CveId)
			} else {
				logs.Error("UpdateCveRelat, update cve_security_notice failed, CveId:", cve.CveId, ",err: ", err)
				o.Rollback()
				return err
			}
		} else {
			sec.CveId = cve.CveId
			var err error
			var num int64
			if num, err = o.Insert(sec); err == nil {
				logs.Info("UpdateCveRelat, insert cve_security_notice success, num: ", num, ", cveNum: ", cve.CveNum)
			} else {
				logs.Error("UpdateCveRelat, insert cve_security_notice failed, CveNum:", cve.CveNum, ",err:", err)
				o.Rollback()
				return err
			}
		}
		var sc Score
		scError := o.Raw("select id from cve_score where cve_id = ?", cve.CveId).QueryRow(&sc)
		if scError == nil {
			var num int64
			score.Id = sc.Id
			var err error
			if num, err = o.Update(score); err == nil {
				logs.Info("UpdateCveRelat, update cve_score success, num: ", num, ", cve.CveId: ", cve.CveId)
			} else {
				logs.Error("UpdateCveRelat, update cve_score failed, CveId:", cve.CveId, ",err: ", err)
				o.Rollback()
				return err
			}
		} else {
			score.CveId = cve.CveId
			if num, err := o.Insert(score); err == nil {
				logs.Info("UpdateCveRelat, insert cve_score success, num: ", num, ", cveNum:", cve.CveNum)
			} else {
				logs.Error("UpdateCveRelat, insert cve_score failed, CveNum: ", cve.CveNum, ",err: ", err)
				o.Rollback()
				return err
			}
		}
	} else {
		logs.Error("UpdateCveRelat, Transaction creation failed, CveId:", cve.CveId, ",errs: ", errs)
		return errs
	}
	o.Commit()
	return nil
}

func UpdateCveRelat1(cve *VulnCenter, sec *SecurityNotice) (err error) {
	o := orm.NewOrm()
	errs := o.Begin()
	if errs == nil {
		v := VulnCenter{CveId: cve.CveId}
		if err = o.Read(&v, "CveId"); err == nil {
			var num int64
			if num, err = o.Update(cve); err == nil {
				logs.Info("UpdateCveRelat1, update cve_vuln_center success, num: ", num, ", cveNum", cve.CveNum)
			} else {
				logs.Error("UpdateCveRelat1, update cve_vuln_center failed, CveId:", cve.CveId, ",err: ", err)
				o.Rollback()
				return err
			}
		} else {
			var num int64
			if num, err = o.Insert(cve); err == nil {
				logs.Info("UpdateCveRelat1, insert cve_vuln_center success, num: ", num, ", cveNum:", cve.CveNum)
			} else {
				logs.Error("UpdateCveRelat1, insert cve_vuln_center failed, CveNum:", cve.CveNum, ",err:", err)
				o.Rollback()
				return err
			}
		}
		se := SecurityNotice{CveId: cve.CveId}
		if err = o.Read(&se, "CveId"); err == nil {
			var num int64
			if num, err = o.Update(sec); err == nil {
				logs.Info("UpdateCveRelat1, update cve_security_notice success, num: ", num, ", cve.CveId:", cve.CveId)
			} else {
				logs.Error("UpdateCveRelat1, update cve_security_notice failed, CveId:", cve.CveId, ",err: ", err)
				o.Rollback()
				return err
			}
		} else {
			sec.CveId = cve.CveId
			if num, err := o.Insert(sec); err == nil {
				logs.Info("UpdateCveRelat1, insert cve_security_notice success, num: ", num, ", cveNum:", cve.CveNum)
			} else {
				logs.Error("UpdateCveRelat1, insert cve_security_notice failed, CveNum:", cve.CveNum, ",err:", err)
				o.Rollback()
				return err
			}
		}
	} else {
		logs.Error("UpdateCveRelat1, Transaction creation failed, CveId:", cve.CveId, ",err: ", errs)
		return errs
	}
	o.Commit()
	return nil
}

func UpdateCveRelat2(cve *VulnCenter, score *Score) (err error) {
	o := orm.NewOrm()
	errs := o.Begin()
	if errs == nil {
		v := VulnCenter{CveId: cve.CveId}
		if err = o.Read(&v, "CveId"); err == nil {
			var num int64
			if num, err = o.Update(cve); err == nil {
				logs.Info("UpdateCveRelat2, update cve_vuln_center success, num: ", num, ", cveNum: ", cve.CveNum)
			} else {
				logs.Error("UpdateCveRelat2, update cve_vuln_center failed, CveId:", cve.CveId, ", err: ", err)
				o.Rollback()
				return err
			}
		} else {
			var num int64
			if num, err = o.Insert(cve); err == nil {
				logs.Info("UpdateCveRelat2, insert cve_vuln_center success, num: ", num, ", cveNum:", cve.CveNum)
			} else {
				logs.Error("UpdateCveRelat2, insert cve_vuln_center failed, CveNum:", cve.CveNum, ",err:", err)
				o.Rollback()
				return err
			}
		}
		sc := Score{CveId: cve.CveId}
		if err = o.Read(&sc, "CveId"); err == nil {
			var num int64
			if num, err = o.Update(score); err == nil {
				logs.Info("UpdateCveRelat2, update cve_score success, num: ", num, ", cve.CveId", cve.CveId)
			} else {
				logs.Error("UpdateCveRelat2, update cve_score failed, CveId:", cve.CveId, ",err: ", err)
				o.Rollback()
				return err
			}
		} else {
			score.CveId = cve.CveId
			if num, err := o.Insert(score); err == nil {
				logs.Info("UpdateCveRelat2, insert cve_score success, num: ", num, ", cveNum:", cve.CveNum)
			} else {
				logs.Error("UpdateCveRelat2, insert cve_score failed, CveNum:", cve.CveNum, ",err: ", err)
				o.Rollback()
				return err
			}
		}
	} else {
		logs.Error("UpdateCveRelat2, Transaction creation failed, CveId:", cve.CveId, ",errs: ", errs)
		return errs
	}
	o.Commit()
	return nil
}

func QueryOpenSaLastId() (OpenEulerSA, error) {
	o := orm.NewOrm()
	var os OpenEulerSA
	err := o.Raw("select openeuler_id, openeuler_sa_num from cve_open_euler_s_a order by openeuler_id desc limit 1").QueryRow(&os)
	return os, err
}

func QueryOpenSaById(cveId int64) (OpenEulerSA, error) {
	o := orm.NewOrm()
	var os OpenEulerSA
	err := o.Raw("select openeuler_id, openeuler_sa_num from cve_open_euler_s_a where cve_id = ?", cveId).QueryRow(&os)
	return os, err
}

func QueryOriginUpstreamRecord(our *OriginUpstreamRecord, ids, cveNum string) error {
	o := orm.NewOrm()
	err := o.Raw("select * from cve_origin_upstream_record where cve_un_ids = ? "+
		"and cve_num = ? order by cve_record_id desc limit 1", ids, cveNum).QueryRow(our)
	return err
}

func UpdateOriginUpstreamRecord(ogc *OriginUpstreamRecord, fields ...string) error {
	o := orm.NewOrm()
	_, err := o.Update(ogc, fields...)
	return err
}

func UpdateOriginStatus(updateTime, pakName, version string, cveId int64, status int) bool {
	o := orm.NewOrm()
	res, err := o.Raw("UPDATE cve_origin_upstream SET "+
		"cve_status = ?, update_time = ? where cve_id = ? and git_packname = ? and version = ?",
		status, updateTime, cveId, pakName, version).Exec()
	if err == nil {
		num, _ := res.RowsAffected()
		if num > 0 {
			logs.Info("UpdateOriginStatus, cve_origin_upstream row affected nums: ",
				num, ",cveId: ", cveId, ",", updateTime, pakName, version)
			return true
		}
		return false
	} else {
		logs.Error("UpdateOriginStatus, cve_origin_upstream, Update failed, cveId: ",
			cveId, ",", updateTime, pakName, version, ", err: ", err)
		return false
	}
}

func LockUpdateOriginStatus(updateTime, pakName, version string, cveId int64, befStatus int8, status int) bool {
	o := orm.NewOrm()
	res, err := o.Raw("UPDATE cve_origin_upstream SET "+
		"cve_status = ?, update_time = ? where cve_id = ? and "+
		"git_packname = ? and version = ? and cve_status = ?",
		status, updateTime, cveId, pakName, version, befStatus).Exec()
	if err == nil {
		num, _ := res.RowsAffected()
		if num > 0 {
			logs.Info("LockUpdateOriginStatus, cve_origin_upstream row affected nums: ",
				num, ",cveId: ", cveId, ",", updateTime, pakName, version)
			return true
		}
		return false
	} else {
		logs.Error("LockUpdateOriginStatus, cve_origin_upstream, Update failed, cveId: ",
			cveId, ",", updateTime, pakName, version, ", err: ", err)
		return false
	}
}

func UnLockUpdateOriginStatus(beforeStatus, afterStatus int, updateTime, beforeTime string) bool {
	o := orm.NewOrm()
	res, err := o.Raw("UPDATE cve_origin_upstream SET "+
		"cve_status = ?, update_time = ? where cve_status = ? and update_time < ?",
		afterStatus, updateTime, beforeStatus, beforeTime).Exec()
	if err == nil {
		num, _ := res.RowsAffected()
		if num > 0 {
			logs.Info("UnLockUpdateOriginStatus, cve_origin_upstream row affected nums: ",
				num)
			return true
		}
		return false
	} else {
		logs.Error("UnLockUpdateOriginStatus, update failed, cve_origin_upstream,  err: ", err)
		return false
	}
}

func UpdateOriginExcelStatus(updateTime string, cveId int64, status int) bool {
	o := orm.NewOrm()
	res, err := o.Raw("UPDATE cve_origin_excel SET "+
		"cve_status = ?, update_time = ? where cve_id = ?", status, updateTime, cveId).Exec()
	if err == nil {
		num, _ := res.RowsAffected()
		if num > 0 {
			logs.Info("UpdateOriginExcelStatus, cve_origin_excel row affected nums: ", num,
				",cveId: ", cveId, ",updateTime: ", updateTime)
			return true
		}
		return false
	} else {
		logs.Error("UpdateOriginExcelStatus, cve_origin_excel, cveId: ", cveId,
			",updateTime: ", updateTime, ", err: ", err)
		return false
	}
}

func LockUpdateOriginExcelStatus(updateTime string, cveId int64, status int) bool {
	o := orm.NewOrm()
	res, err := o.Raw("UPDATE cve_origin_excel SET "+
		"cve_status = ?, update_time = ? where cve_id = ? and cve_status in (0,1)",
		status, updateTime, cveId).Exec()
	if err == nil {
		num, _ := res.RowsAffected()
		if num > 0 {
			logs.Info("LockUpdateOriginExcelStatus, cve_origin_excel row affected nums: ",
				num, ",cveId: ", cveId, ",", updateTime)
			return true
		}
		return false
	} else {
		logs.Error("LockUpdateOriginExcelStatus, cve_origin_excel, cveId: ", cveId,
			",updateTime: ", updateTime, ", err: ", err)
		return false
	}
}

func UnLockUpdateOriginExcelStatus(beforeStatus, afterStatus int, updateTime, beforeTime string) bool {
	o := orm.NewOrm()
	res, err := o.Raw("UPDATE cve_origin_excel SET "+
		"cve_status = ?, update_time = ? where cve_status = ? and update_time < ?",
		afterStatus, updateTime, beforeStatus, beforeTime).Exec()
	if err == nil {
		num, _ := res.RowsAffected()
		if num > 0 {
			logs.Info("UnLockUpdateOriginExcelStatus, cve_origin_excel row affected nums: ",
				num)
			return true
		}
		return false
	} else {
		logs.Error("UnLockUpdateOriginExcelStatus, update failed, cve_origin_excel,  err: ", err)
		return false
	}
}

func GetSaNumCountByYears(years string, status int) int64 {
	o := orm.NewOrm()
	if status == 0 {
		cnt, err := o.QueryTable("cve_sa_number").Filter("sa_years", years).Count()
		if err == nil {
			logs.Info("GetSaNumCountByYears, cve_sa_number search result：", cnt)
		} else {
			logs.Error("GetSaNumCountByYears, cve_sa_number, Query result failed, err: ", err)
		}
		return cnt
	} else {
		cnt, err := o.QueryTable("cve_sa_number").Filter("sa_years", years).Filter("status", status).Count()
		if err == nil {
			logs.Info("GetSaNumCountByYears, cve_sa_number search result：", cnt)
		} else {
			logs.Error("GetSaNumCountByYears, cve_sa_number, Query result failed, err: ", err, years, status)
		}
		return cnt
	}
}

func GetSaNumberByYears(sa *SaNumber, years string, status, orderSort int) error {
	o := orm.NewOrm()
	err := error(nil)
	if status == 0 {
		if orderSort == 1 {
			err = o.Raw("select * from cve_sa_number where sa_years = ? "+
				"order by sa_id asc limit 1", years).QueryRow(&sa)
			logs.Info("GetSaNumberByYears", err)
		} else {
			err = o.Raw("select * from cve_sa_number where sa_years = ? "+
				"order by sa_id desc limit 1", years).QueryRow(&sa)
			logs.Info("GetSaNumberByYears, err: ", err)
		}
	} else {
		if orderSort == 1 {
			err = o.Raw("select * from cve_sa_number where sa_years = ? and status = ? "+
				"order by sa_id asc limit 1", years, status).QueryRow(&sa)
			logs.Info("GetSaNumberByYears", err)
		} else {
			err = o.Raw("select * from cve_sa_number where sa_years = ? and status = ? "+
				"order by sa_id desc limit 1", years, status).QueryRow(&sa)
			logs.Info("GetSaNumberByYears, err: ", err)
		}
	}
	return err
}

func DeleteSaNumberByYears(years string, status int) {
	o := orm.NewOrm()
	err := o.Raw("delete from cve_sa_number "+
		"where sa_years != ? and status = ? ", years, status).QueryRow()
	logs.Info("DeleteSaNumberByYears", err)
}

func GetSaNumber(sa *SaNumber, field ...string) error {
	o := orm.NewOrm()
	err := o.Read(sa, field...)
	return err
}

func InsertIssueDeleteRecord(idr *IssueDeleteRecord) error {
	o := orm.NewOrm()
	id, err := o.Insert(idr)
	logs.Info("InsertIssueDeleteRecord, id: ", id, ", err: ", err)
	return err
}

func InsertSaNumber(sa *SaNumber) error {
	o := orm.NewOrm()
	id, err := o.Insert(sa)
	logs.Info("InsertSaNumber, id: ", id, ", err: ", err)
	return err
}

func UpdateSaNumber(sa *SaNumber, field ...string) bool {
	o := orm.NewOrm()
	_, err := o.Update(sa, field...)
	if err != nil {
		logs.Error("UpdateSaNumber, err: ", err)
		return false
	}
	return true
}

func UpdateVulnCenter(cve *VulnCenter, field ...string) bool {
	o := orm.NewOrm()
	_, err := o.Update(cve, field...)
	if err != nil {
		logs.Error("UpdateVulnCenter, err: ", err)
		return false
	}
	return true
}

func GetVulnCenterByCid(cve *VulnCenter, field ...string) error {
	o := orm.NewOrm()
	err := o.Read(cve, field...)
	return err
}

func GetCanExportVulnCenterData() (data []VulnCenter, err error) {
	o := orm.NewOrm()
	sql := fmt.Sprintf(
		`SELECT * from cve_vuln_center WHERE is_export IN (0,2,3) and organizate_id = 1 and create_time > %s`,
		time.Now().AddDate(-1, 0, 0).Format(time.DateTime),
	)
	_, err = o.Raw(sql).QueryRows(&data)
	logs.Info("GetCanExportVulnCenterData, err: ", err)
	return
}

func GetCvePackageList(secId int64, affectBranch string) (data []Package, err error) {
	o := orm.NewOrm()
	qs := o.QueryTable("cve_package")
	_, err = qs.Filter("sec_id", secId).Filter("affect_product", affectBranch).OrderBy("source").All(&data)
	return
}

func GetCanExportVulnCenterCount() (count int64) {
	sql := ` SELECT COUNT(*) total FROM (SELECT COUNT(*) num,bc.cve_id,bc.cve_num 
 FROM (SELECT DISTINCT cve_num FROM cve_vuln_center WHERE is_export = 3) ac 
 JOIN cve_vuln_center bc ON ac.cve_num = bc.cve_num GROUP BY bc.cve_num) a`
	res := struct {
		Total int64
	}{}
	o := orm.NewOrm()
	err := o.Raw(sql).QueryRow(&res)
	if err != nil {
		logs.Error("GetCanExportVulnCenterCount, err: ", err)
		return 0
	}
	return res.Total
}

func GetCanExportTheme(cveNums, component, affectBranch string) (string, error) {
	if cveNums == "" || component == "" {
		return "", errors.New("param is empty")
	}
	s := strings.Split(cveNums, ";\n")
	for k, v := range s {
		s[k] = "'" + v + "'"
	}
	cveNums = strings.Join(s, ",")
	type tmp = struct {
		OpenEulerScore float64 `orm:"column(openeuler_score)"`
		Theme          string  `orm:"column(theme)"`
		AffectProduct  string  `orm:"column(affect_product)"`
	}
	res := make([]tmp, 0)
	sql := fmt.Sprintf(`SELECT b.openeuler_score,a.theme,a.affect_product FROM cve_issue_template b 
JOIN  cve_security_notice a ON a.cve_id = b.cve_id
WHERE b.cve_num IN (%s)
AND b.owned_component = '%s'`, cveNums, component)
	o := orm.NewOrm()
	_, err := o.Raw(sql).QueryRows(&res)
	if err != nil {
		return "", err
	}
	max := float64(0)
	resStr := ""
	for _, v := range res {
		if v.AffectProduct == "" || len(v.AffectProduct) < 2 {
			continue
		}
		if max < v.OpenEulerScore {
			max = v.OpenEulerScore
			if affectBranch != "" && len(affectBranch) > 1 {
				resStr = strings.ReplaceAll(v.Theme, v.AffectProduct, affectBranch)
			} else {
				resStr = v.Theme
			}
		}
	}
	return resStr, nil
}

func GetCanExportCveData(page int64, pageSize int) (list []ExcelExport, err error) {
	sql := `SELECT b.num,c.*,a.owned_component,a.cve_brief,
d.sec_id,d.introduction,d.summary,d.theme,d.description,d.influence_component,
d.affect_product,d.reference_link,d.affect_status,e.public_date,openeuler_sa_num
FROM cve_issue_template a 
RIGHT JOIN (SELECT COUNT(*) num,bc.cve_id,bc.cve_num FROM 
(SELECT DISTINCT cve_num FROM cve_vuln_center WHERE is_export = 3) ac 
JOIN cve_vuln_center bc ON ac.cve_num = bc.cve_num GROUP BY bc.cve_num  LIMIT ?,? ) b 
ON a.cve_id = b.cve_id
LEFT JOIN cve_score c
ON b.cve_id = c.cve_id
LEFT JOIN cve_security_notice d
ON b.cve_id = d.cve_id
LEFT JOIN cve_open_euler_s_a e
ON b.cve_id = e.cve_id
`
	o := orm.NewOrm()
	_, err = o.Raw(sql, page, pageSize).QueryRows(&list)
	return
}

// GetCanExportExcelData Get exportable data
func GetCanExportExcelData(cveNum, issueNum, repo string, issueId int64) (list []ExcelExport, err error) {
	if cveNum == "" {
		return list, errors.New("cve number can not empty")
	}
	sql := `SELECT b.num,a.issue_num,a.owned_component,a.cve_brief,a.cve_num,a.openeuler_score,a.openeuler_vector,
d.sec_id,d.introduction,d.summary,d.theme,d.description,d.influence_component,
d.affect_product,d.reference_link,d.affect_status,
e.public_date,e.openeuler_sa_num,a.cve_level,b.organizate_id,a.affected_version,a.analysis_version,a.issue_label
FROM cve_issue_template a 
RIGHT JOIN 
(SELECT (SELECT COUNT(*) FROM cve_vuln_center  WHERE cve_num = ? AND is_export in (0,3) AND pack_name = ? AND organizate_id = 1) num,
bc.cve_id,bc.cve_num,bc.organizate_id 
FROM cve_vuln_center bc WHERE bc.cve_num = ? AND bc.is_export in (0,3) AND bc.pack_name = ? AND bc.organizate_id = 1) b
ON a.cve_id = b.cve_id
LEFT JOIN cve_security_notice d
ON b.cve_id = d.cve_id
LEFT JOIN cve_open_euler_s_a e
ON b.cve_id = e.cve_id
WHERE a.issue_num = ? and a.issue_id = ? and b.organizate_id = 1
`
	o := orm.NewOrm()
	_, err = o.Raw(sql, cveNum, repo, cveNum, repo, issueNum, issueId).QueryRows(&list)
	return
}

func GetIssueNumber(packName string, cves []string) (issueTemp []IssueTemplate, err error) {
	var sql string
	if len(cves) == 0 {
		sql = `select * from cve_issue_template where cve_id in (select cve_id from cve_vuln_center where cve_status = 2 and 
is_export in (0,3) and pack_name in ('%s') and organizate_id = 1) and status < 4`
	} else {
		var s string
		for _, v := range cves {
			s += "'" + v + "',"
		}
		if len(s) > 1 {
			s = s[:len(s)-1]
		}
		sql = `select * from cve_issue_template where cve_id in (select cve_id from cve_vuln_center where cve_status = 2 and 
is_export in (0,3) and pack_name in ('%s') and organizate_id = 1) and status < 4` + ` and cve_num in (` + s + `)`
	}
	sql = fmt.Sprintf(sql, packName)
	o := orm.NewOrm()
	_, err = o.Raw(sql).QueryRows(&issueTemp)
	return
}

func GetUnffectIssueNumber(startTime string, cves []string) (issueTemp []IssueTemplate, err error) {
	sql := `SELECT * FROM cve_issue_template WHERE STATUS in (1,2,3,5) AND issue_status in (1,2,3,6) AND cve_id IN (
SELECT DISTINCT cve_id FROM cve_vuln_center WHERE cve_status = 2 AND
is_export IN (0,3) and organizate_id = 1) AND create_time >= '%s'`

	sql = fmt.Sprintf(sql, startTime)
	o := orm.NewOrm()
	_, err = o.Raw(sql).QueryRows(&issueTemp)
	return
}

func QueryIssueCount(branchVersion string) (count int64) {
	sql := ""
	if len(branchVersion) > 2 {
		branchVersion = "%" + branchVersion + "%"
		sql = fmt.Sprintf(`SELECT COUNT(*) total FROM cve_issue_template where 
affected_version like '%v' and status != 6`, branchVersion)
	} else {
		sql = `SELECT COUNT(*) total FROM cve_issue_template where 
affected_version != '' and status != 6`
	}

	res := struct {
		Total int64
	}{}
	o := orm.NewOrm()
	err := o.Raw(sql).QueryRow(&res)
	if err != nil {
		logs.Error("QueryIssueCount, err: ", err)
		return 0
	}
	return res.Total
}

type CveIssueInfoData struct {
	IssueNum       string  `orm:"column(issue_num)"`
	CveNum         string  `orm:"column(cve_num)"`
	OpeneulerScore float64 `orm:"column(openeuler_score)"`
	NvdScore       float64 `orm:"column(nvd_score)"`
	AffectProduct  string  `orm:"column(affected_version)"`
}

// QueryIssue query issuewhitelist
func QueryCveIssueByBranch(currentPage, pageSize int, milestone string) ([]CveIssueInfoData, error) {
	startSize := (currentPage - 1) * pageSize
	o := orm.NewOrm()
	var irw []CveIssueInfoData
	if len(milestone) > 2 {
		milestone = "%" + milestone + "%"
		num, err := o.Raw("SELECT t.cve_num, t.nvd_score, t.openeuler_score, t.issue_num, t.affected_version"+
			" FROM cve_issue_template t where t.status != 6 and t.affected_version like ? limit ? offset ?", milestone, pageSize, startSize).QueryRows(&irw)
		if err == nil && num > 0 {
			logs.Info("QueryCveIssueByBranch1, search num: ", num)
		} else {
			logs.Info("QueryCveIssueByBranch1, cur_time:",
				common.GetCurTime(), ",err: ", err)
		}
		return irw, err
	} else {
		num, err := o.Raw("SELECT t.cve_num, t.nvd_score, t.openeuler_score, t.issue_num, t.affected_version"+
			" FROM cve_issue_template t where t.status != 6 and t.affected_version != '' limit ? offset ?", pageSize, startSize).QueryRows(&irw)
		if err == nil && num > 0 {
			logs.Info("QueryCveIssueByBranch2, search num: ", num)
		} else {
			logs.Info("QueryCveIssueByBranch2, cur_time:",
				common.GetCurTime(), ", err: ", err)
		}
		return irw, err
	}
}

func QueryCveAllIssueCount(communityFlag int, startTime string) (count int64) {
	var err error

	res := struct {
		Total int64
	}{}

	var sql1 = `SELECT COUNT(t.template_id) total FROM cve_issue_template t, cve_vuln_center v where t.status != 6 %s order by t.update_time desc`
	var sql2 = `SELECT COUNT(t.template_id) total FROM cve_issue_template t where t.status != 6 %s order by t.update_time desc`

	o := orm.NewOrm()
	if len(startTime) > 2 {
		if communityFlag > 0 {
			err = o.Raw(fmt.Sprintf(sql1, "and v.organizate_id = ? and t.cve_id = v.cve_id and t.create_time >= ?"),
				communityFlag, startTime,
			).QueryRow(&res)
		} else {
			err = o.Raw(fmt.Sprintf(sql2, "and t.create_time >= ?"), startTime).QueryRow(&res)
		}
	} else {
		if communityFlag > 0 {
			err = o.Raw(fmt.Sprintf(sql1, "and v.organizate_id = ? and t.cve_id = v.cve_id"), communityFlag).QueryRow(&res)
		} else {
			err = o.Raw(fmt.Sprintf(sql2, "")).QueryRow(&res)
		}
	}

	if err != nil {
		logs.Error("QueryCveAllIssueCount, err: ", err)
	}
	count = res.Total

	return
}

type CveAllIssueData struct {
	Status          int8      `orm:"column(status)"`
	IssueNum        string    `orm:"column(issue_num)"`
	CveNum          string    `orm:"column(cve_num)"`
	OpeneulerScore  float64   `orm:"column(openeuler_score)"`
	NvdScore        float64   `orm:"column(nvd_score)"`
	AffectedVersion string    `orm:"column(affected_version)"`
	CveLevel        string    `orm:"column(cve_level)"`
	CreateTime      time.Time `orm:"column(create_time)"`
	CpublicTime     string    `orm:"column(repair_time)"`
	FirstPerTime    string    `orm:"column(first_per_time)"`
	FirstGetTime    string    `orm:"column(first_get_time)"`
	RpmReleaseTime  string    `orm:"column(rpm_release_time)"`
	SaReleaseTime   string    `orm:"column(sa_release_time)"`
	OwnedVersion    string    `orm:"column(owned_version)"`
	OwnedComponent  string    `orm:"column(owned_component)"`
	Repo            string    `orm:"column(repo)"`
	Owner           string    `orm:"column(owner)"`
	OrganizateId    int8      `orm:"column(organizate_id)"`
	IssueCreate     string    `orm:"column(issue_create)"`
	IsIgnore        int8      `orm:"column(is_ignore)"`
	AffectUpdate    int8      `orm:"column(affect_update)"`
	PackageTime     string    `orm:"column(package_time)"`
}

// QueryIssue query issuewhitelist
func QueryCveAllIssueData(currentPage, pageSize, communityFlag int, startTime string) (res []CveAllIssueData, err error) {
	startSize := (currentPage - 1) * pageSize
	o := orm.NewOrm()
	var sql = `SELECT t.cve_num, t.nvd_score, t.openeuler_score, t.issue_num,t.status, t.affected_version,
       t.owned_version,t.is_ignore,t.owned_component,t.owner,t.repo,t.cve_level,t.create_time,v.repair_time,
       s.sa_release_time,s.rpm_release_time,v.first_per_time,v.first_get_time,v.affect_update,v.package_time,
       o.issue_create
FROM cve_vuln_center v,cve_issue_template t left join cve_issue_template_association s 
on t.template_id = s.template_id left join cve_gite_origin_issue o on t.issue_num = o.number and t.issue_id = o.issue_id
where t.status != 6 and t.cve_id = v.cve_id %s order by t.update_time desc limit ? offset ?`
	if len(startTime) > 2 {
		if communityFlag > 0 {
			_, err = o.Raw(fmt.Sprintf(sql, "and t.create_time >= ? and v.organizate_id = ?"),
				startTime, communityFlag, pageSize, startSize,
			).QueryRows(&res)
		} else {
			_, err = o.Raw(fmt.Sprintf(sql, "and t.create_time >= ?"), startTime, pageSize, startSize).QueryRows(&res)
		}
	} else {
		if communityFlag > 0 {
			_, err = o.Raw(fmt.Sprintf(sql, "and v.organizate_id = ?"), communityFlag, pageSize, startSize).QueryRows(&res)
		} else {
			_, err = o.Raw(fmt.Sprintf(sql, ""), pageSize, startSize).QueryRows(&res)
		}
	}

	if err != nil {
		logs.Error("QueryCveAllIssueData failed, err: ", err)
	}

	return
}

// AllIssueForZeroDay model data from database
type AllIssueForZeroDay struct {
	Status          int8    `orm:"column(status)" json:"status"`
	IssueNum        string  `orm:"column(issue_num)" json:"issue_num"`
	CveNum          string  `orm:"column(cve_num)" json:"cve_num"`
	Description     string  `orm:"column(cve_desc)" json:"description"`
	OpeneulerScore  float64 `orm:"column(openeuler_score)" json:"openeuler_score"`
	NvdScore        float64 `orm:"column(nvd_score)" json:"nvd_score"`
	NVDVector       string  `orm:"column(nvd_vector)" json:"nvd_vector"`
	AffectedVersion string  `orm:"column(affected_version)" json:"affected_version"`
	AnalysisVersion string  `orm:"column(analysis_version)" json:"analysis_version"`
	CveLevel        string  `orm:"column(cve_level)" json:"cve_level"`
	CreateTime      string  `orm:"column(create_time)" json:"create_time"`
	RepairTime      string  `orm:"column(repair_time)" json:"cve_public_time"`
	RpmReleaseTime  string  `orm:"column(rpm_release_time)" json:"-"`
	SaReleaseTime   string  `orm:"column(sa_release_time)" json:"-"`
	OwnedVersion    string  `orm:"column(owned_version)" json:"owned_version"`
	Repo            string  `orm:"column(repo)" json:"repo"`
	Owner           string  `orm:"column(owner)" json:"owner"`
}

// QueryAllIssueForZeroDay get issue data from database
func QueryAllIssueForZeroDay(currentPage, pageSize, communityFlag int, startTime string,
) ([]AllIssueForZeroDay, error) {
	var res []AllIssueForZeroDay
	var err error

	startSize := (currentPage - 1) * pageSize
	o := orm.NewOrm()
	var sql = `SELECT t.cve_num, t.nvd_score,t.nvd_vector, t.openeuler_score, t.issue_num,t.status,
       t.affected_version,t.analysis_version,t.owned_version,t.owner,t.repo,t.cve_level,t.create_time,
       v.repair_time,v.cve_desc,s.sa_release_time,s.rpm_release_time
FROM cve_vuln_center v,cve_issue_template t left join cve_issue_template_association s on t.template_id = s.template_id
where t.status != 6 and t.cve_id = v.cve_id %s order by t.update_time desc limit ? offset ?`
	if startTime != "" {
		if communityFlag > 0 {
			_, err = o.Raw(fmt.Sprintf(sql, "and t.create_time >= ? and v.organizate_id = ?"),
				startTime, communityFlag, pageSize, startSize,
			).QueryRows(&res)
		} else {
			_, err = o.Raw(fmt.Sprintf(sql, "and t.create_time >= ?"), startTime, pageSize, startSize).QueryRows(&res)
		}
	} else {
		if communityFlag > 0 {
			_, err = o.Raw(fmt.Sprintf(sql, "and v.organizate_id = ?"), communityFlag, pageSize, startSize).QueryRows(&res)
		} else {
			_, err = o.Raw(fmt.Sprintf(sql, ""), pageSize, startSize).QueryRows(&res)
		}
	}

	if err != nil {
		logs.Error("QueryCveAllIssueData failed, err: ", err)
	}

	return res, err
}

// QueryPatches query patches
func QueryPatches(cveNum string) []string {
	info, err := QueryCveOriginPatchInfo(cveNum)
	if err != nil {
		logs.Error("QueryCveOriginPatchInfo error: ", cveNum, err)
		return nil
	}

	data := sets.NewString()
	for _, v := range info {
		data.Insert(v.FixPatch)
	}

	return data.List()
}

// QueryReference query reference
func QueryReference(cveNum string) []string {
	info, err := QueryCveOriginReference(cveNum)
	if err != nil {
		logs.Error("QueryCveOriginPatchInfo error: ", cveNum, err)
		return nil
	}

	data := sets.NewString()
	for _, v := range info {
		data.Insert(v.Url)
	}

	return data.List()
}

func QueryFirstPatchTime(cveNum string) string {
	t := OriginUpstreamPatchFirstTime{CveNum: cveNum}
	orm.NewOrm().Read(&t, "cve_num")

	return t.CreateAt
}

func QueryCveOrgIssueCount(communityFlag int) (count int64) {
	sql := ""
	if communityFlag > 0 {
		sql = fmt.Sprintf(`SELECT COUNT(id) total FROM cve_gite_origin_issue where organizate_id = %d order by id desc`, communityFlag)
	} else {
		sql = fmt.Sprintf(`SELECT COUNT(id) total FROM cve_gite_origin_issue order by id desc`)
	}
	res := struct {
		Total int64
	}{}
	o := orm.NewOrm()
	err := o.Raw(sql).QueryRow(&res)
	if err != nil {
		logs.Error("QueryCveOrgIssueCount, err: ", err)
		return 0
	}
	return res.Total
}

// QueryIssue query issuewhitelist
func QueryCveOrgIssueData(currentPage, pageSize, communityFlag int) (goi []GiteOriginIssue) {
	startSize := (currentPage - 1) * pageSize
	o := orm.NewOrm()
	if communityFlag > 0 {
		num, err := o.Raw("SELECT * FROM cve_gite_origin_issue where organizate_id = ? order by id desc limit ? offset ?",
			communityFlag, pageSize, startSize).QueryRows(&goi)
		if err == nil && num > 0 {
			logs.Info("QueryCveOrgIssueData1, search num: ", num)
		} else {
			logs.Info("QueryCveOrgIssueData1, cur_time:",
				common.GetCurTime(), ",err: ", err)
		}
	} else {
		num, err := o.Raw("SELECT * FROM cve_gite_origin_issue order by id desc limit ? offset ?", pageSize, startSize).QueryRows(&goi)
		if err == nil && num > 0 {
			logs.Info("QueryCveOrgIssueData2, search num: ", num)
		} else {
			logs.Info("QueryCveOrgIssueData2, cur_time:",
				common.GetCurTime(), ",err: ", err)
		}
	}
	return
}

// QueryIssue query issuewhitelist
func QueryCveSingleIssueData(communityFlag int, packName, cveNum, issueNum string) (cai CveAllIssueData, err error) {
	o := orm.NewOrm()
	err = o.Raw("SELECT t.cve_num, t.nvd_score, t.openeuler_score, t.issue_num, t.affected_version,t.owned_version,t.owned_component,"+
		"t.cve_level,t.create_time,v.repair_time,s.sa_release_time,s.rpm_release_time,v.first_per_time, v.first_get_time"+
		" FROM cve_vuln_center v,cve_issue_template t left join cve_issue_template_association s on t.template_id = s.template_id "+
		"where t.status != 6 and t.cve_id = v.cve_id and v.organizate_id = ? "+
		"and v.pack_name=? and v.cve_num=? and t.issue_num=? limit 1", communityFlag, packName, cveNum, issueNum).QueryRow(&cai)
	if err != nil {
		err = o.Raw("SELECT t.cve_num, t.nvd_score, t.openeuler_score, t.issue_num, t.affected_version,t.owned_version,t.owned_component,"+
			"t.cve_level,t.create_time,v.repair_time,s.sa_release_time,s.rpm_release_time,v.first_per_time, v.first_get_time"+
			" FROM cve_vuln_center v,cve_issue_template t left join cve_issue_template_association s on t.template_id = s.template_id "+
			"where t.status != 6 and t.cve_id = v.cve_id and v.organizate_id = ? "+
			"and v.pack_name=? and v.cve_num=? limit 1", communityFlag, packName, cveNum).QueryRow(&cai)
	}
	return
}

func GetCveVulnCenter(cve string) ([]VulnCenter, error) {
	sql := "select * from cve_vuln_center where cve_num = ?"
	var res []VulnCenter
	o := orm.NewOrm()
	_, err := o.Raw(sql, cve).QueryRows(&res)
	return res, err
}

func QueryCveIssue(cves []string, communityFlag int) (res []CveAllIssueData, err error) {
	o := orm.NewOrm()
	var cve string
	for _, v := range cves {
		cve = "'" + v + "'," + cve
	}
	if len(cve) > 1 {
		cve = cve[:len(cve)-1]
	}

	var sql = `SELECT t.cve_num, t.nvd_score, t.openeuler_score, t.issue_num, t.affected_version,t.owned_version,
t.owned_component,t.owner,t.repo,t.cve_level,t.create_time,v.repair_time,s.sa_release_time,s.rpm_release_time,v.first_per_time,
v.first_get_time,v.organizate_id,o.issue_create FROM cve_vuln_center v,cve_issue_template t left join cve_issue_template_association s 
on t.template_id = s.template_id left join cve_gite_origin_issue o on t.issue_num = o.number and t.issue_id = o.issue_id 
where t.status != 6 and t.cve_id = v.cve_id %s order by t.update_time desc`
	if communityFlag != 0 {
		_, err = o.Raw(
			fmt.Sprintf(sql, fmt.Sprintf("and t.cve_num in (%s) and v.organizate_id = ?", cve)), communityFlag,
		).QueryRows(&res)
	} else {
		_, err = o.Raw(
			fmt.Sprintf(sql, fmt.Sprintf("and t.cve_num in (%s)", cve)),
		).QueryRows(&res)
	}

	return
}

func QueryCvePackIssue(data []Cve, communityFlag int) ([]CveAllIssueData, error) {
	var sql = `SELECT t.cve_num, t.nvd_score, t.openeuler_score, t.issue_num, t.affected_version,t.owned_version,t.owned_component,t.owner,t.repo,
t.cve_level,t.create_time,v.repair_time,s.sa_release_time,s.rpm_release_time,v.first_per_time, v.first_get_time, v.organizate_id
FROM cve_vuln_center v,cve_issue_template t left join cve_issue_template_association s on t.template_id = s.template_id 
where t.status != 6 and t.cve_id = v.cve_id and t.cve_num = ? and t.owned_component = ? %s order by t.update_time desc`
	o := orm.NewOrm()
	if communityFlag != 0 {
		sql = fmt.Sprintf(sql, "and v.organizate_id = "+strconv.Itoa(communityFlag))
	} else {
		sql = fmt.Sprintf(sql, "")
	}

	var res = make([]CveAllIssueData, 0)

	for _, v := range data {
		var r []CveAllIssueData
		o.Raw(sql, v.CveNum, v.Pack).QueryRows(&r)
		if len(r) == 0 {
			continue
		}
		res = append(res, r...)
	}

	if len(res) == 0 {
		return nil, errors.New("not found")
	}

	return res, nil
}

type sbomIssue struct {
	IssueTemplate
	ScoreType string `orm:"column(score_type)"`
}

func QueryCveIssueForSbom(pkgname, version string) (data []sbomIssue, err error) {
	o := orm.NewOrm()
	version = "%" + version + "%"
	sql := `select a.*,b.score_type from cve_issue_template a left join cve_score b on a.cve_id = b.cve_id where a.owned_component = '%s' and a.owned_version like '%s'`

	_, err = o.Raw(fmt.Sprintf(sql, pkgname, version)).QueryRows(&data)
	return
}

// QueryIssueToUpdateAffected 应该增加时间限制
func QueryIssueToUpdateAffected(cveNum string, packages []string) ([]IssueTemplate, error) {
	qs := orm.NewOrm().QueryTable(IssueTemplate{})
	qs = qs.Filter("owner", "src-openeuler").Filter("status__in", StatusOpen, StatusProgress, StatusSuspend)
	if cveNum != "" {
		qs = qs.Filter("cve_num", cveNum)
	} else {
		qs = qs.Filter("owned_component__in", packages)
	}

	var data []IssueTemplate

	_, err := qs.All(&data)

	return data, err
}

func QueryUpdatedAffected(page, pageSize int) (data []UpdatedAffectedBranch, err error) {
	qs := orm.NewOrm().QueryTable("cve_updated_affected_branch")
	offset := (page - 1) * pageSize

	_, err = qs.Limit(pageSize, offset).All(&data)

	return
}

// QueryPackageByBranch 根据分支查询包信息
func QueryPackageByBranch(branch string) ([]OpenEulerRepoOrigin, error) {
	var data []OpenEulerRepoOrigin

	qs := orm.NewOrm().QueryTable(OpenEulerRepoOrigin{})
	_, err := qs.Filter("branch__contains", branch).All(&data)

	return data, err
}

// GetPackageTime get package time by name
func (u *OriginUpstream) GetPackageTime(packageName string) string {
	var packageTime []common.PackageTime
	if err := json.Unmarshal([]byte(u.PackageTime), &packageTime); err != nil {
		return ""
	}

	for _, v := range packageTime {
		if v.Name == packageName {
			return v.AddTime
		}
	}

	return ""
}
